package com.kool.kadmin.common.util;

import com.kool.kadmin.common.constant.ErrorCode;
import com.kool.kadmin.common.exception.AppException;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @description:
 * @author: luyu
 * @create: 2019/02/01 17:20
 */
public class ExcelUtil {
    public static final String EXCEL_SUFFIX_XLS = "xls";
    public static final String EXCEL_SUFFIX_XLSX = "xlsx";

    /**
     * 读取excel文件
     *
     * @param filePath
     * @param sheetBeg
     * @param sheetEnd
     * @return
     */
    public static List<List<List<String>>> read(String filePath,  int sheetBeg, int sheetEnd) {

        File excelFile = new File(filePath);
        String filename = excelFile.getName();
        Workbook book = null;
        List<List<List<String>>> listSheet;
        try {
            InputStream inputStream = new FileInputStream(excelFile);
            if (filename.endsWith(EXCEL_SUFFIX_XLS)) {
                book = new HSSFWorkbook(inputStream);
            } else if (filename.endsWith(EXCEL_SUFFIX_XLSX)) {
                book = new XSSFWorkbook(inputStream);
            }
            listSheet = new ArrayList<>();
            if (sheetEnd > book.getNumberOfSheets()) {
                sheetEnd = book.getNumberOfSheets();
            }
            for (int i = sheetBeg; i < sheetEnd; i++) {
                List<List<String>> sheet = getSheetValue(book, i, 0, 65535, 0, 65535);
                listSheet.add(sheet);
            }
            book.close();
        } catch (FileNotFoundException e) {

            throw new AppException(ErrorCode.SYSTEM_ERROR, e, StringUtil.getUUID());
        } catch (IOException e) {
            throw new AppException(ErrorCode.SYSTEM_ERROR, e, StringUtil.getUUID());
        }
        return listSheet;
    }

    /**
     * @param book
     * @param sheetIndex
     * @param rowBegin
     * @param rowEnd
     * @param cellBegin
     * @param cellEnd
     * @return
     * @throws IOException
     * @DESCRIBE 获取excel的一页数据
     * @DATE 2017年8月25日 下午9:07:57
     */
    private static List<List<String>> getSheetValue(Workbook book, int sheetIndex, int rowBegin, int rowEnd, int cellBegin,
                                                    int cellEnd) {
        Sheet sheet = book.getSheetAt(sheetIndex);
        List<List<String>> listRow = new ArrayList<List<String>>();
        if (rowEnd > sheet.getLastRowNum()) {
            rowEnd = sheet.getLastRowNum();
        }
        for (int i = rowBegin; i <= rowEnd; i++) {
            Row row = sheet.getRow(i);
            List<String> listCell = new ArrayList<String>();
            if (cellEnd > row.getLastCellNum()) {
                cellEnd = row.getLastCellNum();
            }
            for (int j = 0; j < cellEnd; j++) {
                Cell cell = row.getCell(j);
                String cellValue = getCellValue(cell);
                listCell.add(cellValue);
            }
            listRow.add(listCell);
        }

        return listRow;
    }

    /**
     * @param cell
     * @return
     * @DESCRIBE 转单元格数据的格式，暂时只需要String类型
     * @DATE 2017-08-23 21:35:42
     */
    private static String getCellValue(Cell cell) {
        if (cell == null) {
            return "";
        }
        cell.setCellType(CellType.STRING);
        return String.valueOf(cell.getStringCellValue());
    }

    /**
     * 生成excel文件
     *
     * @param filePath 文件路径
     * @param data     3维矩阵数据
     */
    public static void write(String filePath, List<List<List<String>>> data) {
        Workbook book = null;
        if (filePath.endsWith(EXCEL_SUFFIX_XLS)) {
            book = new HSSFWorkbook();
        } else if (filePath.endsWith(EXCEL_SUFFIX_XLSX)) {
            book = new XSSFWorkbook();
        }
        for (int sheetIndex = 0; sheetIndex < data.size(); sheetIndex++) {
            Sheet sheet = book.createSheet("sheet" + String.valueOf(sheetIndex));
            List<List<String>> sheetData = data.get(sheetIndex);
            for (int rowIndex = 0; rowIndex < sheetData.size(); rowIndex++) {
                Row row = sheet.createRow(rowIndex);
                List<String> rowData = sheetData.get(rowIndex);
                for (int cellIndex = 0; cellIndex < rowData.size(); cellIndex++) {
                    Cell cell = row.createCell(cellIndex);
                    cell.setCellValue(rowData.get(cellIndex));
                }
            }
        }

        File excelFile = new File(filePath);
        File parentFile = excelFile.getParentFile();
        if (!parentFile.exists()) {
            parentFile.mkdirs();
        }
        FileOutputStream fileOutputStream = null;
        try {
            fileOutputStream = new FileOutputStream(excelFile);
            book.write(fileOutputStream);
        } catch (IOException e) {
            throw new AppException(ErrorCode.BUSINESS_ERROR, e, "生成excel文件失败！");
        } finally {
            if (book != null) {
                try {
                    book.close();
                } catch (IOException e) {
                    throw new AppException(ErrorCode.BUSINESS_ERROR, e, "生成excel文件失败！");
                }
            }
            if (fileOutputStream != null) {
                try {
                    fileOutputStream.close();
                } catch (IOException e) {
                    throw new AppException(ErrorCode.BUSINESS_ERROR, e, "生成excel文件失败！");
                }
            }
        }
    }

    public static void main(String[] args) {
        List<String> row1 = new ArrayList<>();
        row1.add("1");
        row1.add("2");
        row1.add("3");
        row1.add("4");
        row1.add("5");
        List<String> row2 = new ArrayList<>();
        row2.add("6");
        row2.add("7");
        row2.add("8");
        row2.add("9");
        row2.add("10");

        List<List<String>> sheet1 = new ArrayList<>();
        sheet1.add(row1);
        sheet1.add(row2);
        List<List<List<String>>> data = new ArrayList<>();
        data.add(sheet1);

        write("./deploy/模板样式.xlsx", data);
    }
}
